unit CustomerManagementForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, uADStanIntf, uADStanOption, UDF,
  uADStanError, uADGUIxIntf, uADPhysIntf, uADStanDef, uADStanPool, uADStanAsync,
  uADPhysManager, uADGUIxFormsWait, uADStanParam, uADDatSManager, uADDAptIntf,
  uADDAptManager, Data.DB, uADCompDataSet, uADCompClient, uADCompGUIx,
  uADPhysIB, uADPhysMySQL, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids, Data.DBXMySQL,
  Data.SqlExpr, Vcl.ComCtrls, Vcl.ExtCtrls, Vcl.ExtDlgs, System.UITypes,
  Vcl.Imaging.pngimage, Vcl.Imaging.jpeg, Vcl.Imaging.GifImg, Vcl.Buttons;

type
  TfmCustomer = class(TForm)
    ADPhysMySQLDriverLink1: TADPhysMySQLDriverLink;
    ADPhysIBDriverLink1: TADPhysIBDriverLink;
    ADConnection1: TADConnection;
    ADGUIxWaitCursor1: TADGUIxWaitCursor;
    adtCustomers: TADTable;
    dsCustomers: TDataSource;
    DBGrid1: TDBGrid;
    Label1: TLabel;
    btnNew: TButton;
    btnEdit: TButton;
    btnDelete: TButton;
    btnClose: TButton;
    btnSave: TButton;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Label2: TLabel;
    Label3: TLabel;
    txtFirstName: TEdit;
    Label4: TLabel;
    txtLastName: TEdit;
    Label5: TLabel;
    dpBirthday: TDateTimePicker;
    txtCustomerCode: TEdit;
    Label6: TLabel;
    txtNewICNo: TEdit;
    Label7: TLabel;
    txtAddress: TEdit;
    txtAddress2: TEdit;
    Label8: TLabel;
    txtPostCode: TEdit;
    Label9: TLabel;
    Label10: TLabel;
    txtCity: TEdit;
    cbState: TComboBox;
    Label11: TLabel;
    cbCountry: TComboBox;
    Label12: TLabel;
    cbRace: TComboBox;
    Label13: TLabel;
    cbEthnic: TComboBox;
    imgPhoto: TImage;
    Shape1: TShape;
    btnLoad: TButton;
    OpenPictureDialog1: TOpenPictureDialog;
    qrySQL: TADQuery;
    TabSheet2: TTabSheet;
    TabSheet3: TTabSheet;
    Label14: TLabel;
    txtGFirstName: TEdit;
    Label15: TLabel;
    txtGLastName: TEdit;
    Label16: TLabel;
    txtGNewICNo: TEdit;
    Label17: TLabel;
    dpGBirthday: TDateTimePicker;
    Label20: TLabel;
    txtGAddress: TEdit;
    txtGAddress2: TEdit;
    Label21: TLabel;
    txtGPostCode: TEdit;
    Label22: TLabel;
    txtGCity: TEdit;
    Label23: TLabel;
    cbGState: TComboBox;
    Label24: TLabel;
    cbGCountry: TComboBox;
    btnLoad2: TButton;
    Image1: TImage;
    Shape2: TShape;
    Label25: TLabel;
    txt2Address: TEdit;
    txt2Address2: TEdit;
    Label26: TLabel;
    txt2PostCode: TEdit;
    Label27: TLabel;
    txt2City: TEdit;
    Label28: TLabel;
    cb2State: TComboBox;
    Label29: TLabel;
    cb2Country: TComboBox;
    btnCustomerCode: TSpeedButton;
    procedure FormCreate(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure btnLoadClick(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure btnDeleteClick(Sender: TObject);
    procedure btnEditClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure cb2CountryChange(Sender: TObject);
    procedure cbCountryChange(Sender: TObject);
    procedure cbGCountryChange(Sender: TObject);
    procedure btnCustomerCodeClick(Sender: TObject);
  private
    DBOp: TDBOp;
    CustomerNo: integer;
    procedure SetDetails;
    procedure SaveDetails;
    procedure ClearDetails;
    procedure InitStateList;
    procedure InitCountryList;
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fmCustomer: TfmCustomer;

implementation

uses
  MainForm;

{$R *.dfm}

procedure TfmCustomer.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmCustomer.btnCustomerCodeClick(Sender: TObject);
begin
  with fmMain.SysVariables do
  begin
    CustomerNo := StrToInt(Values['CUSTOMER_CODE_LAST_NO']) + 1;
    txtCustomerCode.Text := Format('%s%.*d', [Values['CUSTOMER_CODE_PREFIX'],
                                               StrToInt(Values['CUSTOMER_CODE_LENGTH']),
                                               CustomerNo]);
  end;
end;

procedure TfmCustomer.btnDeleteClick(Sender: TObject);
var
  AffectedRows: integer;
begin
  if MessageDlg('Confirm delete?', mtConfirmation, [mbOk, mbCancel], 0) = mrCancel then Exit;
  try
    AffectedRows := ADConnection1.ExecSQL('Delete from customers Where ID=:ID', [adtCustomers.FieldByName('ID').AsInteger]);
    if AffectedRows <= 0 then
      ShowMessage('No such record found, no record be deleted.');
    if AffectedRows > 1 then
      ShowMessage('More than one record be deleted!');
    ADConnection1.Commit;
    adtCustomers.Refresh;
    ClearDetails;
  except
    on E : Exception do
    begin
      ShowMessage('Exception message = '+E.Message);
      ADConnection1.Rollback;
    end;
  end;
end;

procedure TfmCustomer.btnEditClick(Sender: TObject);
begin
  SetDetails;
end;

procedure TfmCustomer.btnLoadClick(Sender: TObject);
begin
  if OpenPictureDialog1.Execute(Self.WindowHandle) then
    if FileExists(OpenPictureDialog1.FileName) then
      imgPhoto.Picture.LoadFromFile(OpenPictureDialog1.FileName)
    else
      { Otherwise raise an exception. }
      raise Exception.Create('File does not exist.');
end;

procedure TfmCustomer.btnNewClick(Sender: TObject);
begin
  ClearDetails;
end;

procedure TfmCustomer.btnSaveClick(Sender: TObject);
begin
  if MessageDlg('Confirm Save?', mtConfirmation, [mbYes, mbNo], 0) = mrNo then
    Exit;

  SaveDetails;
  ClearDetails;
end;

procedure TfmCustomer.cb2CountryChange(Sender: TObject);
begin
  InitStateList;
end;

procedure TfmCustomer.cbCountryChange(Sender: TObject);
begin
  InitStateList;
end;

procedure TfmCustomer.cbGCountryChange(Sender: TObject);
begin
  InitStateList;
end;

procedure TfmCustomer.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
begin
  CanClose := True;
  FreeAndNil(fmCustomer);
end;

procedure TfmCustomer.FormCreate(Sender: TObject);
var
  ADPhysDriverLink: TADPhysDriverLink;
  DriverID: string;
begin
  DriverID := DBDriverID;
  if DriverID = 'Firebird' then
    ADPhysDriverLink := ADPhysIBDriverLink1;
  if DriverID = 'MySQL' then
    ADPhysDriverLink := ADPhysMySQLDriverLink1;
  SetDBInfo(ADConnection1, ADPhysDriverLink);
  adtCustomers.Active := True;
  TPicture.RegisterFileFormat('imp','PNG image file',TPngImage);
  TPicture.RegisterFileFormat('jpg','JPG image file',TJPEGImage);
  TPicture.RegisterFileFormat('gif','GIF image file',TGifImage);
  InitCountryList;
  PageControl1.TabIndex := 0;
end;

procedure TfmCustomer.ClearDetails;
begin
  txtCustomerCode.Text := '';
  txtCustomerCode.ReadOnly := False;
  btnCustomerCode.Enabled := True;
  txtFirstName.Text := '';
  txtLastName.Text := '';
  txtNewICNo.Text := '';
  dpBirthday.Date := Now;
  cbEthnic.ItemIndex := -1;
  cbRace.ItemIndex := -1;
  txtAddress.Text := '';
  txtAddress2.Text := '';
  txtPostCode.Text := '';
  txtCity.Text := '';
  cbState.ItemIndex := -1;
  cbCountry.ItemIndex := 0;
  txt2Address.Text := '';
  txt2Address2.Text := '';
  txt2PostCode.Text := '';
  txt2City.Text := '';
  cb2State.ItemIndex := -1;
  cb2Country.ItemIndex := 0;
  txtGFirstName.Text := '';
  txtGLastName.Text := '';
  txtGNewICNo.Text := '';
  dpGBirthday.Date := Now;
  txtGAddress.Text := '';
  txtGAddress2.Text := '';
  txtGPostCode.Text := '';
  txtGCity.Text := '';
  cbGState.ItemIndex := -1;
  cbGCountry.ItemIndex := 0;
  imgPhoto.Picture := nil;
  DBOp := opInsert;
end;

procedure TfmCustomer.InitStateList;
var
  sState: string;
begin
  cbState.Items.Clear;
  cb2State.Items.Clear;
  cbGState.Items.Clear;
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('Select STATE From state Where COUNTRY=:Country');
    Params.ParamByName('Country').Value := cbCountry.Items[cbCountry.ItemIndex];
    if not Prepared then
      Prepare;
    Open;
    while not EOF do
    begin
      sState := FieldByName('STATE').AsString;
      cbState.Items.Add(sState);
      cb2State.Items.Add(sState);
      cbGState.Items.Add(sState);
      Next;
    end;
  end;
end;

procedure TfmCustomer.InitCountryList;
var
  sCountry: string;
begin
  cbCountry.Items.Clear;
  cb2Country.Items.Clear;
  cbGCountry.Items.Clear;
  with qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    SQL.Add('SELECT * FROM country');
    if not Prepared then
      Prepare;
    Open;
    while not EOF do
    begin
      sCountry := FieldByName('COUNTRY').AsString;
      cbCountry.Items.Add(sCountry);
      cb2Country.Items.Add(sCountry);
      cbGCountry.Items.Add(sCountry);
      Next;
    end;
  end;
end;

procedure TfmCustomer.SetDetails;
var
  imgStream: TMemoryStream;
  JpgImg: TJpegImage;
  BlobField: TBlobField;
begin
  txtCustomerCode.Text := adtCustomers.FieldByName('Customer_Code').AsString;
  txtCustomerCode.ReadOnly := True;
  btnCustomerCode.Enabled := False;
  txtFirstName.Text := adtCustomers.FieldByName('FirstName').AsString;
  txtLastName.Text := adtCustomers.FieldByName('LastName').AsString;
  dpBirthday.Date := adtCustomers.FieldByName('Birthday').AsDateTime;
  cbEthnic.ItemIndex := cbEthnic.Items.IndexOf(adtCustomers.FieldByName('Ethnic').AsString);
  cbRace.ItemIndex := cbRace.Items.IndexOf(adtCustomers.FieldByName('Race').AsString);
  txtAddress.Text := adtCustomers.FieldByName('Address1').AsString;
  txtAddress2.Text := adtCustomers.FieldByName('Address2').AsString;
  txtNewICNo.Text := adtCustomers.FieldByName('NewIC').AsString;
  txtPostCode.Text := adtCustomers.FieldByName('PostCode').AsString;
  txtCity.Text := adtCustomers.FieldByName('City').AsString;
  cbState.ItemIndex := cbState.Items.IndexOf(adtCustomers.FieldByName('State').AsString);
  cbCountry.ItemIndex := cbCountry.Items.IndexOf(adtCustomers.FieldByName('Country').AsString);
  txt2Address.Text := adtCustomers.FieldByName('2nd_Address1').AsString;
  txt2Address2.Text := adtCustomers.FieldByName('2nd_Address2').AsString;
  txt2PostCode.Text := adtCustomers.FieldByName('2nd_PostCode').AsString;
  txt2City.Text := adtCustomers.FieldByName('2nd_City').AsString;
  cb2State.ItemIndex := cb2State.Items.IndexOf(adtCustomers.FieldByName('2nd_State').AsString);
  cb2Country.ItemIndex := cb2Country.Items.IndexOf(adtCustomers.FieldByName('2nd_Country').AsString);
  txtGFirstName.Text := adtCustomers.FieldByName('Guarantee_FirstName').AsString;
  txtGLastName.Text := adtCustomers.FieldByName('Guarantee_LastName').AsString;
  dpGBirthday.Date := adtCustomers.FieldByName('Guarantee_Birthday').AsDateTime;
  txtGAddress.Text := adtCustomers.FieldByName('Guarantee_Address1').AsString;
  txtGAddress2.Text := adtCustomers.FieldByName('Guarantee_Address2').AsString;
  txtGNewICNo.Text := adtCustomers.FieldByName('Guarantee_NewIC').AsString;
  txtGPostCode.Text := adtCustomers.FieldByName('Guarantee_PostCode').AsString;
  txtGCity.Text := adtCustomers.FieldByName('Guarantee_City').AsString;
  cbGState.ItemIndex := cbGState.Items.IndexOf(adtCustomers.FieldByName('Guarantee_State').AsString);
  cbGCountry.ItemIndex := cbGCountry.Items.IndexOf(adtCustomers.FieldByName('Guarantee_Country').AsString);
  imgStream := TMemoryStream.Create;
  JpgImg := TJpegImage.Create;
  try
    BlobField := TBlobField(adtCustomers.FieldByName('Photo'));
    BlobField.SaveToStream(imgStream);
    imgStream.Position := 0;
    if imgStream.Size > 1 then
    begin
      JpgImg.LoadFromStream(imgStream);
      imgPhoto.Picture.Assign(JpgImg);
    end;
  finally
    imgStream.Free;
    JpgImg.Free;
  end;
  DBOp := opUpdate;
end;

procedure TfmCustomer.DBGrid1CellClick(Column: TColumn);
begin
  SetDetails;
end;

procedure TfmCustomer.SaveDetails;
var
  imgStream: TMemoryStream;
begin
  with qrySQL do
  begin
    Active := False;
    SQL.Clear;
    Params.Clear;
    case DBOp of
      opInsert: begin
        SQL.Add('insert into customers (customer_code, firstname, lastname, birthday, ' +
                'ethnic, race, address1, address2, newic, postcode, city, state, ' +
                'country, 2nd_address1, 2nd_address2, 2nd_postcode, 2nd_city, 2nd_state, ' +
                '2nd_country, guarantee_firstname, guarantee_lastname, guarantee_birthday, ' +
                'guarantee_address1, guarantee_address2, guarantee_newic, ' +
                'guarantee_postcode, guarantee_city, guarantee_state, ' +
                'guarantee_country, photo, createdate) values (:customercode, ' +
                ':firstname, :lastname, :birthday, :ethnic, :race, :address1, ' +
                ':address2, :newic, :postcode, :city, :state, :country, ' +
                ':saddress1, :saddress2, :spostcode, :scity, :sstate, :scountry, ' +
                ':gfirstname, :glastname, :gbirthday, :gaddress1, ' +
                ':gaddress2, :gnewic, :gpostcode, :gcity, :gstate, ' +
                ':gcountry, :photo, CURRENT_TIMESTAMP);');
      end;
      opUpdate: begin
        SQL.Add('update customers set customer_code = :customercode, ' +
                'firstname=:firstname, ' +
                'lastname=:lastname, ' +
                'birthday=:birthday, ' +
                'ethnic=:ethnic, ' +
                'race=:race, ' +
                'address1=:address1, ' +
                'address2=:address2, ' +
                'newic=:newic, ' +
                'postcode=:postcode, ' +
                'city=:city, ' +
                'state=:state, ' +
                'country=:country, ' +
                '2nd_address1=:saddress1, ' +
                '2nd_address2=:saddress2, ' +
                '2nd_postcode=:spostcode, ' +
                '2nd_city=:scity, ' +
                '2nd_state=:sstate, ' +
                '2nd_country=:scountry, ' +
                'guarantee_firstname=:gfirstname, ' +
                'guarantee_lastname=:glastname, ' +
                'guarantee_birthday=:gbirthday, ' +
                'guarantee_address1=:gaddress1, ' +
                'guarantee_address2=:gaddress2, ' +
                'guarantee_newic=:gnewic, ' +
                'guarantee_postcode=:gpostcode, ' +
                'guarantee_city=:gcity, ' +
                'guarantee_state=:gstate, ' +
                'guarantee_country=:gcountry, ' +
                'photo=:photo where id=:id;');
        Params.ParamByName('ID').AsInteger := adtCustomers.FieldByName('ID').AsInteger;
      end;
    end;
    Params.ParamByName('CustomerCode').AsString := txtCustomerCode.Text;
    Params.ParamByName('FirstName').AsString := txtFirstName.Text;
    Params.ParamByName('LastName').AsString := txtLastName.Text;
    Params.ParamByName('Birthday').AsDate := dpBirthday.Date;
    Params.ParamByName('Ethnic').AsString := cbEthnic.Text;
    Params.ParamByName('Race').AsString := cbRace.Text;
    Params.ParamByName('Address1').AsString := txtAddress.Text;
    Params.ParamByName('Address2').AsString := txtAddress2.Text;
    Params.ParamByName('NewIC').AsString := txtNewICNo.Text;
    Params.ParamByName('PostCode').AsString := txtPostCode.Text;
    Params.ParamByName('City').AsString := txtCity.Text;
    Params.ParamByName('State').AsString := cbState.Text;
    Params.ParamByName('Country').AsString := cbCountry.Text;
    Params.ParamByName('SAddress1').AsString := txt2Address.Text;
    Params.ParamByName('SAddress2').AsString := txt2Address2.Text;
    Params.ParamByName('SPostCode').AsString := txt2PostCode.Text;
    Params.ParamByName('SCity').AsString := txt2City.Text;
    Params.ParamByName('SState').AsString := cb2State.Text;
    Params.ParamByName('SCountry').AsString := cb2Country.Text;
    Params.ParamByName('GFirstName').AsString := txtGFirstName.Text;
    Params.ParamByName('GLastName').AsString := txtGLastName.Text;
    Params.ParamByName('GBirthday').AsDate := dpGBirthday.Date;
    Params.ParamByName('GAddress1').AsString := txtGAddress.Text;
    Params.ParamByName('GAddress2').AsString := txtGAddress2.Text;
    Params.ParamByName('GNewIC').AsString := txtGNewICNo.Text;
    Params.ParamByName('GPostCode').AsString := txtGPostCode.Text;
    Params.ParamByName('GCity').AsString := txtGCity.Text;
    Params.ParamByName('GState').AsString := cbGState.Text;
    Params.ParamByName('GCountry').AsString := cbGCountry.Text;
    if imgPhoto.Picture.Graphic <> nil then
    begin
      imgStream := TMemoryStream.Create;
      try
        imgPhoto.Picture.Graphic.SaveToStream(imgStream);
        imgStream.Position := 0;
        Params.ParamByName('Photo').LoadFromStream(imgStream, ftBlob, 0)
      finally
        imgStream.Free;
      end;
    end else
      Params.ParamByName('Photo').AsByte := 0;
    try
      if not Prepared then
        Prepare;
//      Active := True;
      ExecSQL;
      if RowsAffected <> 1 then
        ShowMessage('Something wrong when save.')
      else begin
        //update sys_variables for current customer number
        with qrySQL do
        begin
          SQL.Clear;
          Params.Clear;
          SQL.Add('Update sys_variables Set VALUE=:Value Where NAME=:Name');
          Params.ParamByName('Value').AsInteger := CustomerNo;
          Params.ParamByName('Name').AsString := 'CUSTOMER_CODE_LAST_NO';
          if not Prepared then
            Prepare;
          ExecSQL;
          if RowsAffected <> 1 then
            ShowMessage('CUSTOMER_CODE_LAST_NO update with error, please check database!');
        end;
      end;
      ADConnection1.Commit;
      adtCustomers.Refresh;
    except
      on E : Exception do
      begin
        ShowMessage('Exception message = '+E.Message);
        ADConnection1.Rollback;
      end;
    end;
  end;
end;

end.
